package com.clive.dao;

import com.clive.bean.Dishes;
import com.clive.bean.DishesCategory;
import com.clive.util.DbcpUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DishesDaoImpl implements DishesDao {
    public int addDishes(Dishes dishes) {
        Connection conn = DbcpUtils.getconnection();
        PreparedStatement pst = null;
        String sql = "INSERT INTO dishes(dish_name,price,buy_conut,pic_path,created,updated,category_id,status) VALUE(?,?,?,?,?,?,?,?)";
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, dishes.getDishName());
            pst.setDouble(2, dishes.getPrice());
            pst.setInt(3, dishes.getBuyconut());
            pst.setString(4, dishes.getPicpath());
            pst.setDate(5, new Date(dishes.getCreated().getTime()));
            pst.setDate(6, new Date(dishes.getUpdated().getTime()));
            pst.setInt(7, dishes.getCategory().getId());
            pst.setInt(8, dishes.getStatus());
            int i = pst.executeUpdate();
            return i;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbcpUtils.closeAll(conn, pst);
        }


        return 0;
    }

    public List<Dishes> findDishesByCurrentPage(int index, Integer limit) {
        Connection conn = DbcpUtils.getconnection();
        PreparedStatement pst = null;
        ResultSet result = null;
        String sql = "SELECT a.id,a.dish_name,a.price,a.pic_path,a.buy_conut,b.dishes_category_name,a.created,a.updated FROM dishes a INNER JOIN dishes_category b ON a.category_id = b.id WHERE a.status = 1 LIMIT ?,?";
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, index);
            pst.setInt(2, limit);
            result = pst.executeQuery();
            List<Dishes> list = new ArrayList<Dishes>();
            while (result.next()) {
                Dishes dishes = new Dishes();
                dishes.setId(result.getInt("id"));
                dishes.setDishName(result.getString("dish_name"));
                dishes.setPrice(result.getDouble("price"));
                dishes.setPicpath(result.getString("pic_path"));
                dishes.setBuyconut(result.getInt("buy_conut"));
                dishes.setCreated(result.getDate("created"));
                dishes.setUpdated(result.getDate("updated"));
                DishesCategory category = new DishesCategory();
                category.setDishesCategoryName(result.getString("dishes_category_name"));
                dishes.setCategory(category);
                list.add(dishes);
            }
            return list;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbcpUtils.closeAll(conn, pst, result);
        }
        return null;
    }

    public int findDIshesByCount() {
        Connection conn = DbcpUtils.getconnection();
        PreparedStatement pst = null;
        ResultSet result = null;
        String sql = "SELECT COUNT(*) FROM dishes a INNER JOIN dishes_category b ON a.category_id = b.id WHERE a.status = 1";
        try {
            pst = conn.prepareStatement(sql);
            result = pst.executeQuery();
            if (result.next()) {
                int count = result.getInt("COUNT(*)");
                return count;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbcpUtils.closeAll(conn, pst, result);
        }
        return 0;
    }

    public int updateDishes(Dishes dishes) {
        Connection conn = DbcpUtils.getconnection();
        PreparedStatement pst = null;
        String sql = "UPDATE dishes SET dish_name = ? ,price = ? ,category_id = ? , updated = ? WHERE id = ?";
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, dishes.getDishName());
            pst.setDouble(2, dishes.getPrice());
            pst.setInt(3, dishes.getCategory().getId());
            pst.setDate(4, new Date(dishes.getUpdated().getTime()));
            pst.setInt(5, dishes.getId());
            int i = pst.executeUpdate();
            return i;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbcpUtils.closeAll(conn, pst);
        }
        return 0;
    }

    public int deleteDishesById(Integer dishesId) {
        Connection conn = DbcpUtils.getconnection();
        PreparedStatement pst = null;
        String sql = "UPDATE dishes SET status = 0 WHERE id = ?";
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, dishesId);
            int i = pst.executeUpdate();
            return i;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbcpUtils.closeAll(conn, pst);
        }
        return 0;
    }

    public List<Dishes> findDishesByName(String dishName) {
        // TODO Auto-generated method stub
        return null;
    }

    public List<Dishes> findDishesByCategoryId(Integer dishesCategoryId) {
        // TODO Auto-generated method stub
        return null;
    }

    public Dishes findDishesById(Integer dishId) {
        Connection conn = DbcpUtils.getconnection();
        PreparedStatement pst = null;
        ResultSet result = null;
        String sql = "SELECT a.id,a.dish_name,a.price,a.pic_path,b.id category_id,b.dishes_category_name FROM dishes a LEFT JOIN dishes_category b ON a.category_id = b.id WHERE a.id = ? AND a.status = 1";
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, dishId);
            result = pst.executeQuery();
            if (result.next()) {
                Dishes dishes = new Dishes();
                dishes.setId(result.getInt("id"));
                dishes.setPrice(result.getDouble("price"));
                dishes.setPicpath(result.getString("pic_path"));
                dishes.setDishName(result.getString("dish_name"));
                DishesCategory category = new DishesCategory();
                category.setId(result.getInt("category_id"));
                category.setDishesCategoryName(result.getString("dishes_category_name"));
                dishes.setCategory(category);
                return dishes;
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbcpUtils.closeAll(conn, pst, result);
        }
        return null;
    }



}
